﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoBe.cs;
using ProjetoUtil;

namespace ProjetoDalc.cs
{
    public class RelatorioPesquisaDalc
    {
        public List<string> SelecionarPesquisas(string sql)
        {
        
            SqlDataReader reader;
            List<string> listapesquisas = new List<string>();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
            
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = sql;

                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        listapesquisas.Add((reader.GetString(0)));
                    }
                }
                conexao.Close();
                return listapesquisas;
            }
        }

        public PesquisaBe BuscarPesquisa(string nomepesquisa)
        {
            SqlDataReader reader;
            PesquisaBe pesquisa = new PesquisaBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommand command = new SqlCommand();

                command.Connection = conexao;
                command.CommandText = "SELECT * FROM PESQUISA WHERE PERGUNTA like '%' + @nome + '%'";
                command.Parameters.AddWithValue("@nome", nomepesquisa);

                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        pesquisa.respostaUm = reader["RESPOSTA_UM"].ToString();
                        pesquisa.respostaDois = reader["RESPOSTA_DOIS"].ToString();
                        pesquisa.respostaTres = reader["RESPOSTA_TRES"].ToString();
                        pesquisa.id = Convert.ToInt32(reader["PESQUISA_ID"]);
                        pesquisa.pergunta = nomepesquisa;
                        
                    }
                }

                conexao.Close();
                return pesquisa;
            }
        }

        public int TrazerQuantidadeResposta(int id, DateTime data, int numero)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT COUNT (RESPOSTA_CRIANCA)FROM RESPOSTA_CRIANCA WHERE PESQUISA_ID = @id AND RESPOSTA_DATA = @data AND RESPOSTA_CRIANCA = @numero";
                command.Parameters.AddWithValue("@id", id);
                command.Parameters.AddWithValue("@data", data);
                command.Parameters.AddWithValue("@numero", numero);

                conexao.Open();
                int usuario = (int)command.ExecuteScalar();
                conexao.Close();

                return usuario;
            }
        }
    }
}
